import cn.hutool.core.convert.Convert;
import cn.hutool.core.io.FileUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;

import java.util.ArrayList;
import java.util.List;

public class jd {
    /**
     * 京东导出的订单处理入口
     */
    public static void main(String[] args) {
        //第一步，打开Excel文件
        ExcelReader reader = ExcelUtil.getReader(FileUtil.file("e:/jd_in.xlsx"));
        List<List<Object>> readAll = reader.read();

        //第二步，精简数据
        readAll = delUnnecessary(readAll);

        //第三步，数据处理
        readAll = dataProcessing(readAll);

        //第四步，保存文件
        ExcelWriter writer = ExcelUtil.getWriter("e:/jd_out.xlsx");
        writer.write(readAll);
        writer.close();
    }

    /**
     * 精简导出表单数据
     *
     * @param readAll 原始表单数据
     * @return 精简后数据
     */
    private static List<List<Object>> delUnnecessary(List<List<Object>> readAll) {
        List<List<Object>> necessary = new ArrayList<List<Object>>();
        for (int i = 0; i < readAll.size(); i++) {
            List<Object> row = new ArrayList<Object>();
            row.add(readAll.get(i).get(0)); //订单号
            row.add(readAll.get(i).get(2)); //商品名称
            row.add(readAll.get(i).get(3)); //订购数量
            row.add(readAll.get(i).get(6)); //京东价
            row.add(readAll.get(i).get(26)); //运费金额
            row.add(readAll.get(i).get(8)); //结算金额
            row.add(readAll.get(i).get(11)); //订单状态
            row.add(readAll.get(i).get(14)); //订单备注
            necessary.add(row);
        }
        return necessary;
    }

    /**
     * 数据处理
     *
     * @param readAll 原始数据
     * @return 处理后数据
     */
    private static List<List<Object>> dataProcessing(List<List<Object>> readAll) {
        for (int i = 1; i < readAll.size() - 1; i++) {
            if (readAll.get(i).get(0).equals(readAll.get(i + 1).get(0))) {
                readAll.get(i).set(4, null);
                readAll.get(i).set(5, null);
            } else {
                if (!readAll.get(i).get(4).equals(0)) {
                    Double total = Convert.toDouble(readAll.get(i).get(4)) + Convert.toDouble(readAll.get(i).get(5));
                    readAll.get(i).set(5, total);
                }
            }
        }
        return readAll;
    }
}
